package org.folio.rest.impl;

import cn.hutool.core.bean.BeanUtil;
import cn.hutool.core.util.StrUtil;
import io.vertx.core.AsyncResult;
import io.vertx.core.Context;
import io.vertx.core.Future;
import io.vertx.core.Handler;
import io.vertx.core.logging.Logger;
import io.vertx.core.logging.LoggerFactory;
import io.vertx.ext.sql.ResultSet;
import org.folio.cql2pgjson.CQL2PgJSON;
import org.folio.rest.RestVerticle;
import org.folio.rest.impl.util.FileExport;
import org.folio.rest.jaxrs.model.ReaderAttendDataCollection;
import org.folio.rest.jaxrs.model.ReaderAttendDataGroup;
import org.folio.rest.jaxrs.resource.PartyReaderAttendData;
import org.folio.rest.persist.PgUtil;
import org.folio.rest.persist.PostgresClient;
import org.folio.rest.tools.utils.ValidationHelper;

import javax.ws.rs.core.Response;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

/**
 * @author lee
 * @Classname PartyReaderAttendDataImpl
 * @Description TODO
 * @Date 2020/6/27 17:56
 * @Created by lee
 */
public class PartyReaderAttendDataImpl implements PartyReaderAttendData {
    private static final Logger logger = LoggerFactory.getLogger("modparty");
    @Override
    public void getPartyReaderAttendData(String query, int offset, int limit, String lang, Map<String, String> okapiHeaders, Handler<AsyncResult<Response>> asyncResultHandler, Context vertxContext) {

        PostgresClient pg = PgUtil.postgresClient(vertxContext, okapiHeaders);

        String cql = null;
        if (!StrUtil.isBlankOrUndefined(query))
        {
           try {
               CQL2PgJSON field = new CQL2PgJSON("a.jsonb");
               cql = field.cql2pgJson(query);
           }catch (Exception e)
           {

           }
        }
        String sql =  this.createSQL(cql,offset,limit,okapiHeaders.get(RestVerticle.OKAPI_HEADER_TENANT));

        pg.select( sql, reply->{
            if (reply.succeeded())
            {
                ResultSet value = reply.result();
                Integer row = value.getNumRows();
                 List<ReaderAttendDataGroup> readerAttendDataGroup = value.getRows()
                         .stream().map(a->BeanUtil.mapToBean(a.getMap(),
                                 ReaderAttendDataGroup.class,true)
                                     ).collect(Collectors.toList());
                ReaderAttendDataCollection ent = new ReaderAttendDataCollection();
                ent.setReaderAttendDataGroup(readerAttendDataGroup);
                ent.setTotalRecords(row);
                asyncResultHandler.handle(Future.succeededFuture(GetPartyReaderAttendDataResponse.respond200WithApplicationJson(ent)));
            }else{
                ValidationHelper.handleError(reply.cause(), asyncResultHandler);
            }
        });
    }

 
    @Override
    public void getPartyReaderAttendDataExportExcel(String query, int offset, int limit, Map<String, String> okapiHeaders, Handler<AsyncResult<Response>> asyncResultHandler, Context vertxContext) {
        PostgresClient pg = PgUtil.postgresClient(vertxContext, okapiHeaders);

        String cql = null;
        if (!StrUtil.isBlankOrUndefined(query))
        {
            try {
                CQL2PgJSON field = new CQL2PgJSON("a.jsonb");
                cql = field.cql2pgJson(query);
            }catch (Exception e)
            {

            }
        }
        Map<String,String> headerAlias = new LinkedHashMap<>();
        headerAlias.put("readeraccount","读者证号");
        headerAlias.put("readername","读者姓名");
        headerAlias.put("reserveamount","报名次数");
        headerAlias.put("attendamount","签到次数");
        headerAlias.put("leave","请假次数");
        headerAlias.put("attendrate","签到率");
        headerAlias.put("leaverate","请假率");
        headerAlias.put("absenteeismrate","缺席率");


        String sql =  this.createSQL(cql,offset,limit,okapiHeaders.get(RestVerticle.OKAPI_HEADER_TENANT));
        pg.select( sql, reply->{
            if (reply.succeeded())
            {
                List<Map<String, Object>> list = reply.result().getRows().stream().map(a -> a.getMap()).collect(Collectors.toList());
                FileExport.fileExports(headerAlias, list, h -> {
                    if (h.succeeded()) {
                        asyncResultHandler.handle(Future.succeededFuture(GetPartyReaderAttendDataExportExcelResponse.respond200WithApplicationOctetStream(h.result())));
                    } else {
                        ValidationHelper.handleError(reply.cause(), asyncResultHandler);
                    }
                });
            }else{
                ValidationHelper.handleError(reply.cause(), asyncResultHandler);
            }
        });
    }



    private String createSQL(String query ,Integer offset ,Integer limit,String tenant){
        StringBuffer stringBuffer = new StringBuffer();
        stringBuffer.append("select f.userName as readerName, f.readerAccount , " +
                "sum(f.count) as reserveAmount, sum(f.checkin) as attendAmount, sum(f.leave) as leave, " +
                "CASE WHEN sum(f.count) = 0 THEN '0.00%' ELSE concat(round(sum(f.checkin)::numeric/sum(f.count),2) * 100, '%') END attendRate, " +
                "CASE WHEN sum(f.count) = 0 THEN '0.00%' ELSE concat(round((sum(f.count) - sum(f.checkin))::numeric/sum(f.count),2) * 100, '%') END absenteeismRate," +
                " CASE WHEN sum(f.count) = 0 THEN '0.00%' ELSE concat(round(sum(f.leave)::numeric/sum(f.count),2) * 100, '%') END leaveRate " +
                "from ( select b.*, count(b.userName) as count, COUNT(case when b.attendanceState = '1' then b.userName end) as checkIn," +
                " COUNT(case when b.attendanceState = '0' then b.userName end) as absence, " +
                "COUNT(case when b.attendanceState = '3' then b.userName end) as leave" +
                " from (select ((a.jsonb -> 'readerReserveGroup')->0 )->>'name' as userName, c.jsonb ->> 'partyName' as activityName," +
                " c.jsonb ->> 'attendState' as attendanceState, ((c.jsonb -> 'readerReserveGroup')->0 )->>'barcode'  as readerAccount from "+tenant+"_mod_party.reserve as" +
                " a left join "+tenant+"_mod_party.attend as c on a.jsonb ->> 'id' = c.jsonb ->> 'reserveId' where 1 = 1 and a.jsonb ->> 'isDel' = '0' ");
        if (!StrUtil.isBlankOrUndefined(query))
        {
            stringBuffer.append(" AND "+query );
        }
        stringBuffer.append(" ) as b where b.readerAccount is not null group by b.userName, b.activityName, b.attendanceState, ") ;
        stringBuffer.append(" b.readerAccount ) as f group by f.userName, f.readerAccount");
        if (offset !=null && limit !=null)
        {
            stringBuffer.append(" limit " + limit + " offset " + offset + " ;");
        }
        logger.info(" run SQL :{}",stringBuffer.toString());
        return stringBuffer.toString();
    }
}
